﻿Imports ccapps.Web.page
Imports System.Data.OracleClient

Namespace persistent
    Public Class FinancialDebtCollection
        Private Shared ConnStr As String = GlobalVar.OraConnStr

        Private Shared dbPackageName As String = "eu_bill.financialdetails."

        Public Shared Function getcustomerInfoFromCustcode(ByVal pin_custcode As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromCustcode")
            command.Parameters.Add("pin_custcode", OracleType.VarChar).Value = pin_custcode
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, pin_custcode, "null", "null", "null", "Select(getcustomerInfoFromCustcode)", "FinancialDebtCollection", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        Public Shared Function getcustomerInfoFromMSISDN(ByVal pin_MSISDN As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromMSISDN")
            command.Parameters.Add("pin_MSISDN", OracleType.VarChar).Value = pin_MSISDN
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(getcustomerInfoFromMSISDN)", "FinancialDebtCollection", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure GetCustInfoFromCOID (pin_COID IN integer, p_cursor OUT r_cursor_type);
        Public Shared Function getcustomerInfoFromCOID(ByVal pin_COID As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromCOID")
            command.Parameters.Add("pin_COID", OracleType.Int32).Value = pin_COID
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            Dim Query As String = "Select * from dual "
            Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", pin_COID, "", "Select(getcustomerInfoFromCOID)", "FinancialDebtCollection", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure GetCustInfoFromMSISDN (pin_MSISDN IN Varchar2, p_cursor OUT r_cursor_type)
        Public Shared Function GetCustInfoFromMSISDN(ByVal pin_MSISDN As String) As DataTable

            Dim command As New OracleCommand(dbPackageName & "GetCustInfoFromMSISDN")
            command.Parameters.Add("pin_MSISDN", OracleType.VarChar).Value = pin_MSISDN
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            'Dim Query As String = "Select * from dual "
            'Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(btnCheckMsisdn_Click)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure FinancialTransactionDetails (pin_datePrev IN Varchar2,pin_dateCon IN Varchar2,pin_CSID IN integer,p_cursor OUT r_cursor_type)
        Public Shared Function FinancialTransactionDetails(pin_datePrev As String, pin_dateCon As String, pin_CSID As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialTransactionDetails")
            command.Parameters.Add("pin_datePrev", OracleType.VarChar).Value = pin_datePrev
            command.Parameters.Add("pin_dateCon", OracleType.VarChar).Value = pin_dateCon
            command.Parameters.Add("pin_CSID", OracleType.Int32).Value = pin_CSID
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            ' Dim Query As String = "Select * from dual "
            ' Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(btnCheckMsisdn_Click)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure FinancialTranDetailsExtInfo (pin_transid IN integer,p_cursor OUT r_cursor_type)
        Public Shared Function FinancialTranDetailsExtInfo(pin_transid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialTranDetailsExtInfo")
            'command.Parameters.Add("pin_datePrev", OracleType.VarChar).Value = pin_datePrev
            'command.Parameters.Add("pin_dateCon", OracleType.VarChar).Value = pin_dateCon
            command.Parameters.Add("pin_transid", OracleType.Int32).Value = pin_transid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            ' Dim Query As String = "Select * from dual "
            ' Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(btnCheckMsisdn_Click)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'procedure FinancialTranDetailsGridBound (pin_transid IN integer,p_cursor OUT r_cursor_type)
        Public Shared Function FinancialTranDetailsGridBound(pin_transid As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "FinancialTranDetailsGridBound")
            'command.Parameters.Add("pin_datePrev", OracleType.VarChar).Value = pin_datePrev
            'command.Parameters.Add("pin_dateCon", OracleType.VarChar).Value = pin_dateCon
            command.Parameters.Add("pin_transid", OracleType.Int32).Value = pin_transid
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            ' Dim Query As String = "Select * from dual "
            ' Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(btnCheckMsisdn_Click)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function
        'PROCEDURE DisplayDebtCollection (pin_csid IN integer,pin_date1 in varchar2,pin_date2 in varchar2,p_cursor OUT r_cursor_type)

        Public Shared Function DisplayDebtCollection(pin_csid As Integer, pin_date1 As String, pin_date2 As String, pin_valid_status As Integer) As DataTable

            Dim command As New OracleCommand(dbPackageName & "DisplayDebtCollection")
            'command.Parameters.Add("pin_datePrev", OracleType.VarChar).Value = pin_datePrev
            'command.Parameters.Add("pin_dateCon", OracleType.VarChar).Value = pin_dateCon
            command.Parameters.Add("pin_csid", OracleType.Int32).Value = pin_csid
            command.Parameters.Add("pin_date1", OracleType.VarChar).Value = pin_date1
            command.Parameters.Add("pin_date2", OracleType.VarChar).Value = pin_date2
            command.Parameters.Add("pin_valid_status", OracleType.Int32).Value = pin_valid_status
            command.Parameters.Add("p_cursor", OracleType.Cursor).Direction = ParameterDirection.Output
            'LOGLAMA
            ' Dim Query As String = "Select * from dual "
            ' Dim gen As Object = New GPOPrepaidToAveaPostpaidCustomerCreate()
            'persistent.myDBConn.ORAScalar_LOG(Query, "null", "null", "null", pin_MSISDN, "Select(btnCheckMsisdn_Click)", "PriceGroupChange", gen.Session("user"))
            Return ExecuteDataTable(command)


        End Function

        Public Shared Function ExecuteNonQuery(ByVal command As OracleCommand, ByRef MESSAGE As String)
            Dim connection As New OracleConnection(ConnStr)
            command.Connection = connection
            command.CommandType = CommandType.StoredProcedure

            Try
                connection.Open()
                command.ExecuteNonQuery()
            Catch ex As Exception
                MESSAGE = ex.Message.ToString
            Finally
                connection.Close()
            End Try
        End Function
        Public Shared Function ExecuteDataTable(ByVal command As OracleCommand, Optional ByVal directSql As Boolean = 0) As DataTable
            Dim connection As New OracleConnection(ConnStr)
            Dim dataAdapter As New OracleDataAdapter(command)
            Dim dataTable As New DataTable
            Dim dataSet As New DataSet

            command.Connection = connection

            If Not (directSql) Then
                command.CommandType = CommandType.StoredProcedure
            End If

            Try
                connection.Open()
                dataAdapter.Fill(dataSet)
                dataTable = dataSet.Tables(0)
            Catch ex As Exception
            Finally
                connection.Close()
                dataSet.Dispose()
            End Try

            Return dataTable
        End Function
    End Class
End Namespace